package dao;

import model.Conn;
import model.Order;
import model.OrderItem;
import model.User;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 用于操作order表
 */
public class OrderDao {
    Conn conn1 = new Conn();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    public int insertOrder(Connection conn, Order order) throws SQLException {
        order.setStatus(2);

        String sql = "insert into `order` values(0,?,?,?,?,?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        ps.setFloat(1, order.getTotal());
        ps.setInt(2, order.getAmount());
        /**
         * @ToDo order.getStatus() 可能有点问题,数值为0 应该为2 （状态1：未付款 2：已付款 3：已发货 4：已完成）
         */
        ps.setInt(3, order.getStatus());
        ps.setInt(4, order.getPaytype());
        ps.setString(5, order.getName());
        ps.setString(6, order.getPhone());
        ps.setString(7, order.getAddress());

        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String Sdata = simpleDateFormat.format(date);

        ps.setString(8, Sdata);
        ps.setInt(9, order.getUser().getId());
        return ps.executeUpdate();
    }

    public int insertOrderItem(Connection conn, OrderItem item) throws SQLException {
        String sql = "insert into `orderitem` values(0,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        ps.setFloat(1, item.getPrice());
        ps.setInt(2, item.getAmount());
        ps.setInt(3, item.getGoods().getId());
        ps.setInt(4, item.getOrder().getId());
        return ps.executeUpdate();
    }

    public int getLastInsertId(Connection conn) throws SQLException {
        //得到刚 insert 进去记录的主键值，只适用与自增主键;
        String sql = "select last_insert_id()";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        rs.next();
        return rs.getInt(1);
    }

    /**
     * 获取指定用户的订单
     *
     * @param id 用户id
     * @return List<Order>
     * @throws SQLException
     */
    public List<Order> getAllOrderByUserId(int id) throws SQLException {
        List<Order> orderList = new ArrayList<>();
        String sql = "SELECT * from `order` where user_id=? order by datetime desc";
        conn = new Conn().getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        rs = ps.executeQuery();
        while (rs.next()) {
            Order order = new Order();
            order.setId(rs.getInt("id"));
            order.setName(rs.getString("name"));
            order.setTotal(rs.getFloat("total"));
            order.setAddress(rs.getString("address"));
            order.setStatus(rs.getInt("status"));
            order.setPhone(rs.getString("phone"));
            order.setDatetime(rs.getTimestamp("datetime"));
            order.setPaytype(rs.getInt("paytype"));
            orderList.add(order);
        }
        Conn.ClossAll(conn, ps, rs);
        return orderList;
    }

    /**
     * 获取订单的详情通过订单id
     *
     * @param orederId 订单id
     * @return List<OrderItem>
     * @throws SQLException
     */
    public List<OrderItem> getAllOrderitem(int orederId) throws SQLException {
        String sql = "select i.id,i.price,i.amount,g.name from orderitem i,goods g where order_id=? and i.goods_id=g.id";
        conn = conn1.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, orederId);
        rs = ps.executeQuery();
        ArrayList<OrderItem> orderItemList = new ArrayList<>();
        while (rs.next()) {
            float price = rs.getFloat("price");
            int amount = rs.getInt("amount");
            String name = rs.getString("name");
            OrderItem orderItem = new OrderItem();
            orderItem.setPrice(price);
            orderItem.setAmount(amount);
            orderItem.setName(name);
            orderItemList.add(orderItem);
        }
        Conn.ClossAll(conn, ps, rs);
        return orderItemList;
    }

    /**
     * 获取指定页的订单详情通过订单的状态 状态0：全部 状态1：未付款 2：已付款 3：已发货 4：已完成
     *
     * @param status     状态0：全部 状态1：未付款 2：已付款 3：已发货 4：已完成
     * @param pageNumber 第几页
     * @param pageSize   一页的数量
     * @return List<Order>
     */
    public List<Order> selectAllOrderByStatus(int status, int pageNumber, int pageSize) throws SQLException {
        List<Order> listOrder = new ArrayList<>();
        conn = conn1.getConn();
        String sql;
        if (status == 0) {
            sql = "SELECT `user`.`username` AS username, `order`.* FROM `order` INNER JOIN `user` ON `order`.user_id = `user`.id ORDER BY `order`.datetime DESC LIMIT ?, ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pageNumber);
            ps.setInt(2, pageSize);
        } else {
            sql = "SELECT `user`.`username` AS username, `order`.* FROM `order` INNER JOIN `user` ON `order`.user_id = `user`.id where `status` = ? ORDER BY `order`.datetime DESC LIMIT ?, ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, status);
            ps.setInt(2, pageNumber);
            ps.setInt(3, pageSize);
        }
        rs = ps.executeQuery();
        while (rs.next()) {
            Order order = new Order();
            User user = new User();

            order.setId(rs.getInt("id"));
            order.setName(rs.getString("name"));
            order.setTotal(rs.getFloat("total"));
            order.setAddress(rs.getString("address"));
            order.setStatus(rs.getInt("status"));
            order.setPhone(rs.getString("phone"));
            order.setDatetime(rs.getTimestamp("datetime"));
            order.setPaytype(rs.getInt("paytype"));

            user.setUsername(rs.getString("username"));
            order.setUser(user);
            listOrder.add(order);
        }
        Conn.ClossAll(conn, ps, rs);
        return listOrder;
    }

    /**
     * 获取订单的数量通过 status 状态0：全部订单 状态1：未付款 2：已付款 3：已发货 4：已完成
     *
     * @param status 状态0：全部订单 状态1：未付款 2：已付款 3：已发货 4：已完成
     * @return int 数量
     * @throws SQLException
     */
    public int getOrderCount(int status) throws SQLException {
        String sql;
        if (status == 0) {
            sql = "select count(*) acc from `order`";
            conn = conn1.getConn();
            ps = conn.prepareStatement(sql);
        } else {
            sql = "select count(*) acc from `order` where `status`=?";
            conn = conn1.getConn();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, status);
        }
        rs = ps.executeQuery();
        rs.next();
        int acc = rs.getInt("acc");
        Conn.ClossAll(conn, ps, rs);
        return acc;
    }

    /**
     * 修改订单的状态 状态1：未付款 2：已付款 3：已发货 4：已完成
     *
     * @param orderId 订单id
     * @param status  状态1：未付款 2：已付款 3：已发货 4：已完成
     * @return boolean
     * @throws SQLException
     */
    public boolean updateStatus(int orderId, int status) throws SQLException {
        String sql = "UPDATE `cakeshop`.`order` SET `status` = ? WHERE `id` = ?";
        conn = conn1.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1, status);
        ps.setInt(2, orderId);
        int i = ps.executeUpdate();
        Conn.ClossAll(conn, ps, rs);
        return i > 0;
    }

    /**
     * 删除订单通过订单id
     *
     * @param con 数据库连接
     * @param id  订单id
     * @return boolean
     * @throws SQLException
     */
    public boolean deleteOrder(Connection con, int id) throws SQLException {
        String sql = "delete from `order` where id = ?";
        ps = con.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        return i > 0;
    }

    /**
     * 删除订单详情通过订单id
     *
     * @param con 数据库连接
     * @param id  订单id
     * @return boolean
     * @throws SQLException
     */
    public boolean deleteOrderItem(Connection con, int id) throws SQLException {
        String sql = "delete from orderitem where order_id=?";
        ps = con.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        return i > 0;
    }
}
